﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Text.RegularExpressions;

using Microsoft.Office.Interop.Excel;

namespace Djikstria
{
    /// <summary>
    /// Reads excel worksheet and gives access to its cells.
    /// </summary>
    public class ExcelReader
    {
        private Microsoft.Office.Interop.Excel.Application appExcel = null;
        private Workbook workbook = null;
        private _Worksheet worksheet = null;

        #region ========== Constructors&Destructors ==========

        /// <summary>
        /// Prepares ExcelReader to work.
        /// </summary>
        public ExcelReader()
        {
            // Create excel client
            OpenExcelClient();
        }

        /// <summary>
        /// Prepares ExcelReader to work and loads up worksheet.
        /// </summary>
        /// <param name="filePath"></param>
        public ExcelReader(string filePath)
            : this()
        {
            // Load up worksheet
            OpenWorksheet(filePath);
        }

        /// <summary>
        /// Destructor
        /// </summary>
        ~ExcelReader()
        {
            CloseWorksheet();
        }

        #endregion

        #region ========== Methods ==========

        /// <summary>
        /// Loads up worksheet.
        /// </summary>
        /// <param name="filePath"></param>
        public void OpenWorksheet(string filePath)
        {
            // Check given file path
            if (string.IsNullOrWhiteSpace(filePath))
                throw new ArgumentException("Given file path is null or empty.");

            // Create excel client if necessery
            while (this.appExcel == null)
                OpenExcelClient();

            // Load up worksheet
            try
            {
                if (File.Exists(filePath))
                {
                    this.workbook = appExcel.Workbooks.Open(filePath, true, true);
                    this.worksheet = (_Worksheet)this.appExcel.ActiveWorkbook.ActiveSheet;
                }
                else
                    throw new FileNotFoundException(filePath);

                if (!IsWorksheetLoaded)
                    throw new Exception("Worksheet loading fail.");

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Closes worksheet
        /// </summary>
        public void CloseWorksheet()
        {
            try
            {
                workbook.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                appExcel = null;
                worksheet = null;
            }
        }

        /// <summary>
        /// Creates new excel client.
        /// </summary>
        protected void OpenExcelClient()
        {
            this.appExcel = new Application();
            if (appExcel == null)
                throw new Exception("Failed to create instance of excel client!");

            this.appExcel.Visible = false;
        }

        /// <summary>
        /// Checks is the worksheet loaded.
        /// </summary>
        public bool IsWorksheetLoaded
        {
            get
            {
                return workbook != null || worksheet != null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="startPoint"></param>
        /// <param name="endPoint"></param>
        /// <returns></returns>
        public Range GetRange(string startPoint, string endPoint)
        {
            if (IsWorksheetLoaded)
            {
                Range range = this.worksheet.get_Range(startPoint, endPoint);
                return range;
            }
            else
                throw new Exception("Worksheet is not loaded.");
        }

        public int GetRange()
        {
            if (IsWorksheetLoaded)
            {
                int range = this.worksheet.UsedRange.Cells.Columns.Count;
                return range;
            }
            else
                throw new Exception("Worksheet is not loaded.");
        }

        public bool InRange(string symbol)
        {
            int end = GetRange().GetHashCode();
            int start = symbol.GetHashCode();
            if (IsWorksheetLoaded)
            {
                if (end >= start)
                    return true;
                else
                    return false;
            }
            else
                throw new Exception("Worksheet is not loaded.");
        }


        //public int GetRange()
        //{
        //    if (IsWorksheetLoaded)
        //    {
        //        int range = this.worksheet.Rows.Count;
        //        return range;
        //    }
        //    else
        //    {
        //        throw new Exception("Worksheet is not loaded.");
        //    }
        //}

        /// <summary>
        /// Gets cells value.
        /// </summary>
        /// <param name="cellId">Cells ID (like A1)</param>
        /// <returns>Cell value.</returns>
        public string GetCellValue(string cellId)
        {
            Range singleCellRange = GetRange(cellId, cellId);
            string cellVal = Convert.ToString(singleCellRange.Value2);
            return cellVal;
        }

        /// <summary>
        /// Gets cells value.
        /// </summary>
        /// <param name="column">Column letter(s).</param>
        /// <param name="row">Row number.</param>
        /// <returns>Cell value.</returns>
        public string GetCellValue(string column, int row)
        {
            if (string.IsNullOrWhiteSpace(column) || row <= 0)
                throw new ArgumentException("Incorrect column or row.");

            string cellId = column + row;
            return GetCellValue(cellId);
        }

        /// <summary>
        /// Gets cells value.
        /// </summary>
        /// <param name="row">Row index.</param>
        /// <param name="column">Column index.</param>
        /// <returns>Cells value.</returns>
        public string GetCellValue(int row, int column)
        {
            if (row <= 0 || column <= 0)
                throw new ArgumentException("Incorrect column or row.");

            Range singleCellRange = this.worksheet.Cells[row, column];
            string cellVal = Convert.ToString(singleCellRange.Value2);
            return cellVal;
        }

        /// <summary>
        /// Gets columns header.
        /// </summary>
        /// <param name="column">Column</param>
        /// <returns>Header</returns>
        public string GetHeader(int column)
        {
            string symbol;
            string ciag = @"(\$)(\w*):(\$)";
            symbol = this.worksheet.Columns[column].Address;
            Regex rgx = new Regex(ciag);
            string result = rgx.Replace(symbol, "");
            return result;
        }
        #endregion

    }
}
